Google ColaboratoryでBigQueryのクエリ結果を扱ってみる
はじめに
データアナリティクス事業本部のkobayashiです。
Google Colaboratory(以下Colab)でBigQueryのデータを扱うことを思い立った場合、ColabはJupyterをベースに作られているので従来のJupyterでBigQueryのデータを扱う方法でデータを扱うことができました。今回Previewですが、ColabからBigQueryのデータを扱う事がCloud Consoleから簡単に行えるようになりましたので早速試してみました。
BigQuery release notes October 05, 2022 | Google Cloud
ColabでBigQueryのデータを扱う方法
従来はColabからBigQuery情報データを扱う場合にはBigQueryを扱う方法としては%%bigqueryのマジックコマンドを使って以下のようにSQLを実行しその結果を取得して後続の処理で使用していました。
%%bigquery SELECT country_code, country_name, COUNT(DISTINCT region_code) AS num_regions FROM `bigquery-public-data.google_trends.international_top_terms` WHERE refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) GROUP BY country_code, country_name ORDER BY num_regions DESC;
他には公式のライブラリである「Python Client for Google BigQuery — google-cloud-bigquery documentation 」やPandasを使っても同様の操作は可能でした。
これがFeatureアップデートでCloudコンソールのBigQueryのクエリ結果からシームレスにColabでデータを扱えるようになりました。これによりBigQueryでSQLを実行しある程度欲しいデータに整形した後、Colab上でPythonスクリプトを使って追加加工し可視化を行うと行った事が簡単に行えるようになりました。
Colab でデータを探索する | BigQuery | Google Cloud
ColabでBigQueryのデータを扱ってみる
作業の流れとしては以下の流れになります。
- Cloudコンソールを使ってBigQueryでSQLを実行しクエリ結果を得る
- クエリ結果からデータ探索を選択してColabで可視化する
それでは実際に操作してみます。
内容としてはBigQueryの一般公開データセットからcovid19_open_dataのデータを使って任意日の人口比のnew_confirmedを都道府県別に色分けした地図をColabで表示してみます。
Cloudコンソールを使ってBigQueryでSQLを実行しクエリ結果を得る
手順1）はじめに可視化したいデータを取得するSQLを作成しBigQueryで実行します。
手順2）クエリ結果のデータを探索より「Colabノートブックで探索」を押下する。
Colabのノートブックが新規作成されて表示されます。必要なコードがすべて用意された状態でノートブックが開きますので上から順次実行していきます。
クエリ結果からデータ探索を選択してColabで可視化する
手順1）ノートブックのSetupを実行する。
BigQueryからColabで探索を行い、Colabのランタイムが起動するとColabからBigQueryへのアクセス許可を求めるモーダルが表示されるので承認を行います。
手順2）Google認証情報へのアクセス確認が表示されるので許可を選択する。
手順3）Colabを使うアカウントを選択する。
手順4）ColabからGoogleアカウントへのアクセス確認が表示されるので問題なければ許可を選択する。
次のブロックの「Reference SQL syntax from the original job」を実行するとBigQueryで実行するSQLを確認できます。
手順5）「Result set loaded from BigQuery job as a DataFrame」のブロックを実行することでクエリ結果をPandasのDataFrame型でresultsとして取得する。
「Show descriptive statistics using describe()」ブロックで統計情報を確認できます。
resultsにDataFrame型式でクエリ結果が入っているのでこれを加工して可視化します。 日本地図をプロットするのにはPythonで便利なライブラリ「japanmap 」があるのでこちらを使います。はじめに使用するライブラリをインストールする必要があるのでコードブロックを新しく作成してpipでインストールする必要があります。
手順6）コードブロックを作成してライブラリをインストールする。
!pip install japanmap !pip install matplotlib
手順7）japanmapでは都道府県名とカラーコードを渡すことで色分けしてくれるのでresultsを加工する。
from japanmap import pref_names
results['pref_code'] = results['location_key'].str.replace('JP_', '').astype(int)
results['pref_name'] = results['pref_code'].map(lambda x: pref_names[x])
results['new_confirmed_rate'] = results['new_confirmed']/results['population']
df = results.loc[:,['pref_name','new_confirmed_rate']].set_index('pref_name')
手順8）japanmapにて手順7で作成したデータを可視化する。
日本地図で可視化するコードは県別データの可視化 - Qiita の「白地図上で人口を可視化」を参考にしています。
from japanmap import picture
import matplotlib.pyplot as plt
import matplotlib.cm as cm
cmap = plt.get_cmap('jet')
norm = plt.Normalize(vmin=df['new_confirmed_rate'].min(), vmax=df['new_confirmed_rate'].max())
fcol = lambda x: '#' + bytes(cmap(norm(x), bytes=True)[:3]).hex()
plt.colorbar(plt.cm.ScalarMappable(norm, cmap))
plt.imshow(picture(df['new_confirmed_rate'].apply(fcol)));
上記のように任意日の人口比のnew_confirmedを都道府県別に色分けした地図を表示できました。
まとめ
まだPreviewですがBigQueryのクエリ結果をColabで可視化してみました。BigQueryでデータを扱っていてデータいろいろな角度で可視化したいと思った際に従来であればスプレッドシートやデータポータルという方法がありましたがこれにColabが加わったことJupyterの扱いに慣れた人は選択肢が増えたと思います。 またBQMLにはないような手法で機械学習を行いたい場合などには非常に便利なのではないかと思います。
最後まで読んで頂いてありがとうございました。
